from flask import Blueprint, request, jsonify, url_for
from sqlalchemy import func
from datetime import datetime, timedelta
from app.models.user import User
from app.models.score import Score
from app.extensions import db

leaderboard_bp = Blueprint('leaderboard_bp', __name__)


@leaderboard_bp.route('/api/leaderboard', methods=['GET'])
def leaderboard():
    try:
        # 参数获取
        school = request.args.get("school")
        class_name = request.args.get("class_name")
        board_type = request.args.get("type", "total")  # total / day / month

        # 基础查询：User 和 Score 关联
        query = db.session.query(
            User.username,
            User.avatar_url,
            User.school,
            User.class_name,
            func.avg(Score.score).label("total_score")
        ).join(Score, User.openid == Score.openid)

        # 过滤条件
        if school:  # 用户传了学校
            query = query.filter(User.school == school)
            if class_name:  # 用户还传了班级
                query = query.filter(User.class_name == class_name)
        # 如果 school 为空，则不加过滤 → 全部学校

        # 时间范围
        now = datetime.now()
        if board_type == "day":
            start_time = datetime(now.year, now.month, now.day)
            query = query.filter(Score.created_at >= start_time)
        elif board_type == "month":
            start_time = datetime(now.year, now.month, 1)  # 本月第一天零点
            query = query.filter(Score.created_at >= start_time)

        # total 不限制时间

        # 分组 + 排序
        query = query.group_by(User.id).order_by(func.avg(Score.score).desc())

        results = query.all()

        data = [
            {
                "nickname": r.username,
                "avatar": url_for('static', filename=r.avatar_url, _external=True),
                "score": round(r.total_score, 2) if r.total_score is not None else 0
            }
            for r in results
        ]

        return jsonify({
            "code": 200,
            "msg": "success",
            "data": data
        })

    except Exception as e:
        return jsonify({"code": 500, "msg": str(e)}), 500
